package cn.kgc.javaweb.dao.impl;

import cn.hutool.crypto.SecureUtil;
import cn.hutool.json.JSONUtil;
import cn.kgc.javaweb.dao.UserDao;
import cn.kgc.javaweb.dto.UserPageDTO;
import cn.kgc.javaweb.entity.User;
import cn.kgc.javaweb.enums.CodeMsg;
import cn.kgc.javaweb.util.DruidUtil;
import com.alibaba.druid.pool.DruidDataSource;
import com.mysql.jdbc.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

/**
 * @author: mosin
 * @version: v1.0  2022/11/9
 */
public class UserDaoImpl  implements UserDao {
    
    @Override
    public User findByUserNameAndPassword(String username, String password) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from user where username = ?  and password = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username, password);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException("查询失败");
        }
    }

    @Override
    public List<User> selectPage(UserPageDTO userPageDTO) {

        Integer page = userPageDTO.getPage();
        Integer limit = userPageDTO.getLimit();

        Integer start = (page-1)*limit;


        String searchParams = userPageDTO.getSearchParams();

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select *  from user  where 1=1 ";

        ArrayList<Object> params = new ArrayList<>();

        // 搜索分页
        if(searchParams!=null){
            //  username  address
            User user = JSONUtil.toBean(searchParams, User.class);
            String username = user.getUsername();
            String address = user.getAddress();

            if(!StringUtils.isEmptyOrWhitespaceOnly(username)){
                sql+=" and username = ?";
                params.add(username);
            }
            if(!StringUtils.isEmptyOrWhitespaceOnly(address)){
                sql+=" and address = ?";
                params.add(address);
            }
        }
        sql+=" limit ? , ?";
        System.out.println("sql = " + sql);
        params.add(start);
        params.add(limit);
        System.out.println("params = " + params);

        try {
            List<User> users = queryRunner.query(sql, new BeanListHandler<User>(User.class), params.toArray());
            return  users;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new  RuntimeException("分页查询失败");
        }
    }

    @Override
    public Long selectTotal(UserPageDTO userPageDTO) {

        //查询总条数   搜索总条数
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());

        String sql = "select count(*) from user where 1=1 ";

        String searchParams = userPageDTO.getSearchParams();
        ArrayList<Object> params = new ArrayList<>();

        if(searchParams!=null){

            User user = JSONUtil.toBean(searchParams, User.class);
            String address = user.getAddress();
            String username = user.getUsername();

            if(!StringUtils.isEmptyOrWhitespaceOnly(address)){
                sql+=" and address = ?";
                params.add(address);
            }
            if(!StringUtils.isEmptyOrWhitespaceOnly(username)){
                sql+=" and username = ?";
                params.add(username);
            }
        }

        try {
            Long total = queryRunner.query(sql, new ScalarHandler<Long>(),params.toArray());
            return  total;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException("查询总条数失败");
        }
    }

    @Override
    public User selectById(Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select *  from user where id = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), id);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException("查询数据失败！");
        }
    }

    @Override
    public void update(User usr) {
        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update user set address = ? ,tel = ? ,birth = ? where username = ?";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String strBirth = simpleDateFormat.format(usr.getBirth());

        try {
            queryRunner.update(sql, usr.getAddress(), usr.getTel(), strBirth, usr.getUsername());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }

    }

    @Override
    public User selectByUserName(String username) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "select * from user  where username = ?";
        try {
            User user = queryRunner.query(sql, new BeanHandler<User>(User.class), username);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException(CodeMsg.SELECT_FAIL.getMsg());
        }
    }

    @Override
    public void add(User usr) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "insert into user (username,password,tel,address,birth)values(?,?,?,?,?)";
        SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy-MM-dd");
        String strBirth = simpleDateFormat.format(usr.getBirth());
        try {
            int update = queryRunner.update(sql, usr.getUsername(), SecureUtil.md5("888888"),usr.getTel(), usr.getAddress(), strBirth);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException(CodeMsg.ADD_FAIL.getMsg());
        }
    }

    @Override
    public void delete(String ids) {  // 1,2,3  1

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "delete from user where id = ?";

        String[] split = ids.split(",");
        ArrayList<Integer> allId = new ArrayList<>();
        for (String s : split) {
            allId.add(Integer.valueOf(s));
        }

        allId.forEach(id->{
            try {
                queryRunner.update(sql, id);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
                throw new  RuntimeException(CodeMsg.DELETE_FAIL.getMsg());
            }
        });
    }

    @Override
    public void updatePwdById(String newPassword, Integer id) {

        QueryRunner queryRunner = new QueryRunner(DruidUtil.getDataSource());
        String sql = "update user set password = ?  where id = ?";
        try {
            queryRunner.update(sql, SecureUtil.md5(newPassword) ,id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            throw  new RuntimeException(CodeMsg.UPDATE_FAIL.getMsg());
        }
    }

}
